package com.db.sql;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import com.db.ColumnInfoBean;
import com.db.ForeignKey;
import com.excel.read.AllTable;
import com.excel.read.Config;
import com.excel.read.GridSearchTable;

/**
 * sql语句生成
 * @author mike
 *
 */
public class OracleSqlGenerate extends SqlGenerate{

	/**
	 * 值检索本身的信息,比如所有省，所有部门，一般用于SQL节点的最高级,只有两个字段，一个是主键，一个是名字
	 */
	public static NodeParameter getSql(GridSearchTable gst, SqlNode sqlNode, Map keyValue, Map <String, String>params, String control) {
		NodeParameter nodeParameter = new NodeParameter();
		int size = params.keySet().size();
		//单独节点
	
			//顶层节点
			Object[] keys = params.keySet().toArray();
			//varName
			String key = (String) keys[0];
			String value = params.get(key);
			//注入SQL节点
			String fullPath = (String) keyValue.get(control.trim());
			List<Node> aNodeList =  gst.getSqlNode(fullPath, control.trim(), sqlNode);
			SqlLeafNode  sqlLeafNode = (SqlLeafNode) aNodeList.get(aNodeList.size()-1);
			
			
			//表名
			String tableName = sqlLeafNode.getEntity().getNodeName();			
		
			
			
			//查询字段、			
			ColumnInfoBean cfb = Config.getBean(tableName, sqlLeafNode.getNodeName());

			//主键
			ColumnInfoBean cfbPK = Config.getPKBean(tableName);
			nodeParameter.setBeans(new ColumnInfoBean[]{cfbPK, cfb});
			
			//SQL
			String sql = "select " + cfbPK.getName()+", "+sqlLeafNode.getNodeName() + " from " + tableName;
			nodeParameter.setSql(sql);
			
			nodeParameter.setQueryMethod(NodeParameter.SINGLE_FIELD_QUERY);
			
		
		return nodeParameter;
	}
	/**
	 * select
	 * @param sqlLeafNode
	 * @return
	 */
	public static NodeParameter getSingleSql(SqlLeafNode sqlLeafNode) {		
		NodeParameter nodeParameter = new NodeParameter();
		//SQL
		//主键
		String tableName = sqlLeafNode.getEntity().getNodeName();
		ColumnInfoBean cfbPK = Config.getPKBean(tableName);
		//外键
		String fk = Config.getSelfFK(tableName);
		if(fk.equalsIgnoreCase("")) {
			return getSingleSql2(sqlLeafNode);
		}else {
			return getSingleSql3(sqlLeafNode);
		}
		//return nodeParameter;
	}
	
	/**
	 * label
	 * @param sqlLeafNode
	 * @return
	 */
	public static NodeParameter getLabelSql(SqlLeafNode sqlLeafNode) {		
		NodeParameter nodeParameter = new NodeParameter();
		//SQL
		//主键
		String tableName = sqlLeafNode.getEntity().getNodeName();
		ColumnInfoBean cfbPK = Config.getPKBean(tableName);
		String sql = "select " + cfbPK.getName() + "as ID ," + sqlLeafNode.getNodeName() + "as DISPLAYNAME from " + tableName +" where "+cfbPK.getName()+"= ?";
		nodeParameter.setSql(sql);			
		nodeParameter.setQueryMethod(NodeParameter.SINGLE_FIELD_QUERY);					
		return nodeParameter;
	}
	
	
	/**
	 * 自驱动
	 * @return
	 */
	public static NodeParameter getSingleSql3(SqlLeafNode sqlLeafNode) {		
		NodeParameter nodeParameter = new NodeParameter();
		//SQL
		//主键
		String tableName = sqlLeafNode.getEntity().getNodeName();
		ColumnInfoBean cfbPK = Config.getPKBean(tableName);
		//外键
		String fk = Config.getSelfFK(tableName);
		String sql = "select " + cfbPK.getName() + "," + sqlLeafNode.getNodeName() + " from " + tableName +" where "+fk+"=0";
		nodeParameter.setSql(sql);			
		nodeParameter.setQueryMethod(NodeParameter.SINGLE_FIELD_QUERY);					
		return nodeParameter;
	}
	
	/**
	 * 数据字典
	 * @return
	 */
	public static NodeParameter getSingleSql2(SqlLeafNode sqlLeafNode) {		
		NodeParameter nodeParameter = new NodeParameter();
		//SQL
		//主键
		String tableName = sqlLeafNode.getEntity().getNodeName();
		ColumnInfoBean cfbPK = Config.getPKBean(tableName);
		String sql = "select " + cfbPK.getName() + "," + sqlLeafNode.getNodeName() + " from " + tableName;
		nodeParameter.setSql(sql);			
		nodeParameter.setQueryMethod(NodeParameter.SINGLE_FIELD_QUERY);					
		return nodeParameter;
	}
	
	/**
	 * 自驱动，驱动下一级控件
	 */
	public static NodeParameter getSingleNextLevelSql(SqlLeafNode sqlLeafNode) {		
		NodeParameter nodeParameter = new NodeParameter();
		//SQL
		//主键
		String tableName = sqlLeafNode.getEntity().getNodeName();
		ColumnInfoBean cfbPK = Config.getPKBean(tableName);
		//外键
		String fk = Config.getSelfFK(tableName);
		
		String sql = "select " + cfbPK.getName() + "," + sqlLeafNode.getNodeName() + " from " + tableName +" a where a."+fk+"= :"+fk;
		nodeParameter.setSql(sql);			
		nodeParameter.setQueryMethod(NodeParameter.SINGLE_FIELD_QUERY);					
		return nodeParameter;
	}
	
	/**
	 * 
	 * @param sqlNode:Root
	 * @param aList
	 * @return
	 */
	public static NodeParameter getGridSql(SqlNode sqlNode, List<SqlLeafNode> aList) {		
		NodeParameter nodeParameter = new NodeParameter();
		StringBuffer sbf = new StringBuffer();
		sbf.append("select ");
		if(aList.size() > 0) {
			SqlLeafNode sqlLeafNode = aList.get(0);
			//SQL
			//主键
			String tableName = sqlLeafNode.getEntity().getNodeName();
			ColumnInfoBean cfbPK = Config.getPKBean(tableName);
			//外键
			String fk = Config.getSelfFK(tableName);
			sbf.append(sqlLeafNode.getEntity().getQName()+"."+sqlLeafNode.getNodeName());
		}
		for(int i=1; i<aList.size(); i++) {
			SqlLeafNode sqlLeafNode = aList.get(i);
			
			//SQL
			//主键
			String tableName = sqlLeafNode.getEntity().getNodeName();
			ColumnInfoBean cfbPK = Config.getPKBean(tableName);
			//外键
			String fk = Config.getSelfFK(tableName);
			sbf.append(" , "+sqlLeafNode.getEntity().getQName()+"."+sqlLeafNode.getNodeName()+" ");
		}
		
		
		sbf.append("from "+ sqlNode.getNodeName()+" "+sqlNode.getQName()+ " "+conn(sqlNode));
		
		
		
		//String sql = "select " + cfbPK.getName() + "," + sqlLeafNode.getNodeName() + " from " + tableName +" a where a."+fk+"= :"+fk;
		nodeParameter.setSql(sbf.toString());			
		nodeParameter.setQueryMethod(NodeParameter.SINGLE_FIELD_QUERY);					
		return nodeParameter;
	}
	
	/**
	 * 该节点的所有节点
	 * @param sqlNode
	 */
	private static StringBuffer conn(SqlNode sqlNode) {
		StringBuffer sbf = new StringBuffer();
		Map<String, SqlNode> map = sqlNode.getNext();
		Iterator iterator = map.entrySet().iterator();
		while(iterator.hasNext()) {
			Map.Entry entry = (Entry) iterator.next();
			String key= (String) entry.getKey();
			SqlNode value = (SqlNode)entry.getValue();
			ForeignKey fk = Config.getFK(sqlNode, value);
			sbf.append(" left join "+value.getNodeName()+" "+value.getQName()+" on "+sqlNode.getQName()+"."+fk.getFkName()+"="+value.getQName()+"."+fk.getToField());
			
		}
		return sbf;
	}
	
	/**
	 * 列表
	 */
	public static NodeParameter getGridSql(SqlLeafNode sqlLeafNode) {		
		NodeParameter nodeParameter = new NodeParameter();
		//SQL
		//主键
		String tableName = sqlLeafNode.getEntity().getNodeName();
		ColumnInfoBean cfbPK = Config.getPKBean(tableName);
		//外键
		String fk = Config.getSelfFK(tableName);
		
		String sql = "select " + cfbPK.getName() + "," + sqlLeafNode.getNodeName() + " from " + tableName +" a where a."+fk+"= :"+fk;
		nodeParameter.setSql(sql);			
		nodeParameter.setQueryMethod(NodeParameter.SINGLE_FIELD_QUERY);					
		return nodeParameter;
	}
	
	
}
